package com.ccjq.shosestore.order.dao.impl;

import com.ccjq.shosestore.core.dbmanager.DbConnection;
import com.ccjq.shosestore.order.dao.OrdersDao;
import com.ccjq.shosestore.order.model.OrderModel;
import com.ccjq.shosestore.order.model.ShopInfoModel;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 订单查询接口的实现类
 *
 * @author LCY
 * @create 2018-12-05 18:19
 */
public class OrdersDaoImpl implements OrdersDao {
    /**
     * 用于获取当前用户记录总行数的方法
     *
     * @param userId
     * @return
     */
    public int getTotalRows(String userId) {
        String sql = "select count(order_id) from t_order where user_id = '" + userId + "'";
        Connection connection = DbConnection.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = -1;
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                result = rs.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }


    /**
     * 根据userId 和当前行数，展示下一页数据
     *
     * @param userId 用户唯一标识
     * @return
     */
    public List<OrderModel> queryOrdersByUserId(String userId, int currentRow) {

        int pageSize = 4;

        final String sql = "select * from (SELECT ORDER_ID , ORDER_TIME, AMOUNT, rownum as a FROM (SELECT ORDER_ID, ORDER_TIME, AMOUNT \n" +
                "  from T_ORDER\n" +
                " WHERE USER_ID = '" + userId + "'" +
                " order by ORDER_TIME desc )) where a between " + currentRow + " and " + (currentRow + pageSize - 1);

        System.out.println("分页的sql:" + sql);
        Connection connection = DbConnection.getConnection();
        List<OrderModel> orders = new ArrayList<OrderModel>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                OrderModel om = new OrderModel();
                String orderId = rs.getString(1);
                String createTime = rs.getString(2);
                double amout = rs.getDouble(3);
                om.setAmout(amout);
                om.setOrderId(orderId);
                om.setCreateTime(createTime);
                orders.add(om);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(rs, ps, connection);
        }
        return orders;
    }

    /**
     * @param orderId 订单编号
     * @return 返回一个包含所有信息的集合
     */
    public OrderModel queryOrdersByOrderId(String orderId) {
        OrderModel om = new OrderModel();
        //根据订单编号，查询订单详细信息的sql语句
        String queryOrderSql = "" +
                "SELECT " +
                "   A.ORDER_ID," + // 订单编号
                "   A.ORDER_TIME," + // 订单创建时间
                "   A.PAYMENT," + // 支付类型，1微信，2支付宝
                "   A.AMOUNT," + // 总金额
                "   A.RECEIVER," + // 收货人姓名
                "   A.PROVINCE," + // 省份
                "   A.CITY," + // 城市
                "   A.COUNTY," + // 区县
                "   A.ADDRESS," + // 地址
                "   A.ZIPCODE," + // 邮政编码
                "   A.TELPHONE " + // 联系方式
                "FROM " +
                "   T_ORDER A " +
                "where ORDER_ID = '" + orderId + "'";

        //查询订单中商品详细信息的sql语句
        String queryShopInfoSql = "" +
                "select " +
                "   T_PRODUCT.PRODUCT_ID," +
                "   DESCRIPTION," +
                "   QUANTITY," +
                "   T_ORDER_PRODUCT.PRICE," +
                "   (T_ORDER_PRODUCT.PRICE * T_ORDER_PRODUCT.QUANTITY) as subtotal " +
                "from " +
                "   T_ORDER_PRODUCT " +
                "join T_PRODUCT on T_PRODUCT.PRODUCT_ID = T_ORDER_PRODUCT.PRODUCT_ID " +
                "where" +
                "   T_PRODUCT.PRODUCT_ID in " +
                "(" +
                " select " +
                "PRODUCT_ID " +
                "from  T_ORDER_PRODUCT)" +
                " and ORDER_ID = '" + orderId + "'";

        Connection connection = DbConnection.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        PreparedStatement ps2 = null;
        ResultSet rs2 = null;

        ArrayList<ShopInfoModel> list = new ArrayList<ShopInfoModel>();
        try {
            //获取订单表内的信息
            ps = connection.prepareStatement(queryOrderSql);
            rs = ps.executeQuery();
            ps2 = connection.prepareStatement(queryShopInfoSql);
            rs2 = ps2.executeQuery();

            while (rs.next()) {
                om.setOrderId(rs.getString(1));
                om.setCreateTime(rs.getString(2));
                om.setPayment(rs.getInt(3));
                om.setAmout(rs.getDouble(4));
                om.setReceiver(rs.getString(5));
                om.setProvince(rs.getString(6));
                om.setCity(rs.getString(7));
                om.setCounty(rs.getString(8));
                om.setAddress(rs.getString(9));
                om.setZipcode(rs.getString(10));
                om.setTelphone(rs.getString(11));
            }
            //获取商品详细信息
            while (rs2.next()) {
                ShopInfoModel shopInfoModel = new ShopInfoModel();
                shopInfoModel.setProduceId(rs2.getString(1));
                shopInfoModel.setDescripyion(rs2.getString(2));
                shopInfoModel.setQuantity(rs2.getInt(3));
                shopInfoModel.setPrice(rs2.getDouble(4));
                shopInfoModel.setSubtotal(rs2.getDouble(5));
                list.add(shopInfoModel);
            }
            //将信息添加到订单模型中
            om.setShopInfo(list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(rs, ps, connection);
            DbConnection.close(rs2, ps2, null);
        }
        return om;
    }

    /**
     * 向数据库插入订单，OrdersModel数据模型承载订单元素
     * 订单添加成功，返回订单概略列表
     * 订单添加失败，抛出SQLException异常，由购物车项目组负责接受，并进行事务回滚
     *
     * @param sql1   向订单表插入数据
     * @param sql2   向订单与商品关联表插入数据
     * @param userId 用户ID
     * @return 更新后的订单列表（需要支持分页，待实现）
     */
    public List<OrderModel> saveOrder(String sql1, String sql2, String userId) throws SQLException {

        Connection connection = DbConnection.getConnection();
        String[] sqlArr = sql2.split(";");
        Statement statement = connection.createStatement();
        for (String str : sqlArr) {
            System.out.println(str);
            statement.addBatch(str);
        }
        statement.executeBatch();

        PreparedStatement ps1 = connection.prepareStatement(sql1);
        ps1.executeUpdate();

        List<OrderModel> list = this.queryOrdersByUserId(userId, 1);
        return list;
    }
}
